<?php
/**
 * Grid List class
 * @author kinsoncn<kinsoncn@gmail.com> 
 */

class Lin_Grid
{
	private $grid;
	private $db;
	private $sql;
	private $colNames = array('N/A');
	private $colModel = array( array('name' => 'N/A', 'index' => 'N/A'));
	private $key = '';
	private $keyLink = '';
	private $keyLinkId = 0;
	
	private $edit 	= false;
	private $add 	= false;
	private $del	= false;
	private $search = true;	
	private $gridId = '1';
	private $selectRowScript = '';
	
	private $havingField = array();
	
	private $sign = array(
			'eq' =>	 	"= '{xx}'",
			'ne' =>		"!= '{xx}'",
			'lt' =>		"< '{xx}'",
			'le' =>		"<= '{xx}'",
			'gt' =>		"> '{xx}'",
			'ge' =>		">= '{xx}'",
			'bw' =>		"like '{xx}%'",
			'bn' =>		"not like '{xx}%'",
			'in' =>		"instr('{xx}', {x.f.x})",
			'ni' =>		"not instr('{xx}', {x.f.x})",
			'ew' =>		"like '%{xx}'",
			'en' =>		"not like '%{xx}'",
			'cn' =>		"like '%{xx}%'",
			'nc' =>		"not like '%{xx}%'"
	);

	
	public function __construct($id)
	{
		$this->gridId = $id;
		
		$grid->sortable = true;		
		$grid->url = '';
		$grid->datatype = 'json';
		$grid->height = '100%';		
		$grid->colNames = $this->colNames;
		$grid->colModel = $this->colModel;
		$grid->rowNum = 20;
		$grid->autowidth = true;
		$grid->rowList = array(10,20,30,50,100,200);
		$grid->gridview = true;
		$grid->mtype = "GET";
		$grid->pager = "#pager{$this->gridId}";
		$grid->viewrecords = true;
		$grid->sortorder = 'desc';
		$grid->caption = 'Caption';
		$grid->sortname = '';
		$grid->scroll = false;
		
		//判断是否要重载SESSION URL
		if (isset($_SESSION['_GRIDLIST'][$this->gridId]))
		{
			$grid->page = $_SESSION['_GRIDLIST'][$this->gridId]['page'];
			$grid->rowNum = $_SESSION['_GRIDLIST'][$this->gridId]['rows'];
		}		
		
		$this->grid = $grid;
		$this->grid->pager = "#pager{$this->gridId}";		
	}
	
	
	public function setKey($key)
	{
		$this->key = $key;
		return $this;
	}
	
	public function setKeyLink($id, $link)
	{
		$this->keyLinkId = $id;
		$this->keyLink = $link;
		return $this;
	}
	
	public function setEdit($bool)
	{
		$this->edit = $bool;
		return $this;
	}
	
	public function setAdd($bool)
	{
		$this->add = $bool;
		return $this;
	}
	
	public function setDel($bool)
	{
		$this->del = $bool;
		return $this;
	}
	
	public function setSearch($bool)
	{
		$this->search = $bool;
		return $this;
	}
	
	public function setSelectRow($field, $element, $fun)
	{
		$this->selectRowScript .=  $fun && $element && $field ? "
				jQuery(\"{$element}\").click( function(){
					var id = jQuery(\"#list{$this->gridId}\").jqGrid('getGridParam','selrow');
						if (id)	{
							var ret = jQuery('#list{$this->gridId}').jqGrid('getRowData',id);
							var field = ret.{$field};
							eval(\"{$fun}(\" +  field + \")\");
						} else { alert('Please select row');}
					});" : "";
		$this->selectRowScript .= "\n";
		return $this;
	}
	
	public function setHaving($fields)
	{
		$this->havingField = $fields;
		return $this;
	}	


	private function subWhere()
	{
		$where = '';
		$having = '';
		$v = 'where';
		$t = 'wh';
		if (isset($_REQUEST['searchField']) && !empty($_REQUEST['searchField'])
				&& isset($_REQUEST['searchOper']) && !empty($_REQUEST['searchOper'])
				&& isset($_REQUEST['searchString']) && !empty($_REQUEST['searchString'])
				&& isset($_REQUEST['_search']) && $_REQUEST['_search'] == 'true')
		{
			$v = in_array($_REQUEST['searchField'], $this->havingField)? 'having' : 'where' ;
			if ($_REQUEST['searchOper'] == 'in' || $_REQUEST['searchOper'] == 'ni')
			{
				$tmp = ' and ' . $this->sign[$_REQUEST['searchOper']];
				$$v = str_replace('{x.f.x}', $_REQUEST['searchField'], $tmp);
			}
			else
			{
				$tmp = $_REQUEST['searchField'] . ' ' .  $this->sign[$_REQUEST['searchOper']];
				$$v = ' and ' . str_replace('{xx}', $_REQUEST['searchString'], $tmp);
			}
	
		}
		elseif (isset($_REQUEST['filters']) && !empty($_REQUEST['filters']) && isset($_REQUEST['_search']) && $_REQUEST['_search'] == 'true')
		{
			//echo stripslashes($_REQUEST['filters']);exit;
			$op = json_decode(stripslashes($_REQUEST['filters']));
			$wh = array();
			$ha = array();
			foreach ($op->rules as $val)
			{
				$s = in_array($val->field, $this->havingField);
				if ($val->op == 'in' || $val->op == 'ni' )
				{
					$w = "";
					$w = str_replace('{xx}', $val->data ,$this->sign[$val->op]);
					if ($s)
					{
						$ha[] = str_replace('{x.f.x}', $val->field ,$w);
					}
					else
					{
						$wh[] = str_replace('{x.f.x}', $val->field ,$w);
					}
				}
				else
				{
					if ($s)
					{
						$ha[] = $val->field . ' ' . str_replace('{xx}', $val->data ,$this->sign[$val->op]);
					}
					else
					{
						$wh[] = $val->field . ' ' . str_replace('{xx}', $val->data ,$this->sign[$val->op]);
					}
				}
			}
			if (count($wh))
			{
				$where = implode(" {$op->groupOp} ", $wh);
				$where = " and ({$where}) ";
			}
			if (count($ha))
			{
				$having = implode(" {$op->groupOp} ", $ha);
				$having = " and ({$having}) ";
			}
				
		}
			
		$ret = $where;
		if ($having)
		{
			$ret .= " having 1 {$having} ";
		}
		return $ret;
	}	
	
	public function setQuery($sql)
	{
		$this->db = Lin_Db::getInstance();
		$this->sql = $sql;
		$result= $this->db->query($sql);
		$fields = mysql_num_fields($result);
		$this->colNames = array();
		$this->colModel = array();
		for ($i = 0; $i < $fields; $i++)
		{
			$fieldName = mysql_field_name($result, $i);
			$this->colNames[] = $fieldName;
			unset($tmp);
			$tmp->name = $fieldName;
			$tmp->index = $fieldName;
			$tmp->editable = true;
			$tmp->ditoptions->size = 30;
			$this->colModel[] = $tmp;
		}
		$this->grid->sortname = $this->colNames[0];
		$this->grid->colNames = $this->colNames;
		$this->grid->colModel = $this->colModel;	
		return $this;
	}
	
	public function setColName($col)
	{
		foreach ($this->colNames as $key => $val)
		{
			if ($col[$key])
			{
				$this->colNames[$key] = $col[$key];
			}
		}
		
		$this->grid->colNames = $this->colNames;
		return $this;
	}
	
	public function setColModel($id, $attribute)
	{
		foreach ((array)$attribute as $key => $val )
		{
			$this->colModel[$id]->$key = $val;
		}
		$this->grid->colModel = $this->colModel;
		return $this;
	}	
	
	
	public function setGrid($param)
	{
		foreach ((array)$param as $key => $val)
		{
			$this->grid->$key = $val;	
		}
		return $this;
	}
	
	static public function includeSource()
	{
		$html = '<link rel="stylesheet" type="text/css" media="screen" href="/css/smoothness/jquery-ui-1.8.18.custom.css" />		
			<link rel="stylesheet" type="text/css" media="screen" href="/css/ui.jqgrid.css" />		
			<script src="/js/jquery-1.7.1.min.js" type="text/javascript"></script>		
			<script src="/js/i18n/grid.locale-en.js" type="text/javascript"></script>		
			<script src="/js/jquery.jqGrid.min.js" type="text/javascript"></script>
			<script src="/js/jquery-ui-1.8.18.custom.min.js" type="text/javascript"></script>';
		return $html;
	}
	
	public function html()
	{
		$search = $this->search ? 'true':'false';
		$add 	= $this->add ? 'true' : 'false';
		$edit 	= $this->edit ? 'true' : 'false';
		$del 	= $this->edit ? 'true' : 'false';	

		$html = "<table id=\"list{$this->gridId}\"></table>
			<div id=\"pager{$this->gridId}\"></div>		
			<script type=\"text/javascript\">
			jQuery().ready(function (){
				jQuery(\"#list{$this->gridId}\").jqGrid( " . json_encode($this->grid) . " );
				
				jQuery(\"#list{$this->gridId}\").jqGrid('navGrid','#pager{$this->gridId}',
				{edit: {$edit}, add: {$add}, del:$del, search: $search}, //options
				{}, // edit options
				{}, // add options
				{}, // del options
				{multipleSearch:{$search}} 	// search options
				);
				
				jQuery(\"#list{$this->gridId}\").jqGrid('navButtonAdd','#pager{$this->gridId}',{
				    caption: \"Columns\",
				    title: \"Reorder Columns\",
				    onClickButton : function (){
				        jQuery(\"#list{$this->gridId}\").jqGrid('columnChooser');
				    }
				});		
				{$this->selectRowScript}
			});
			</script>";
		return $html;
	}	
	
	private function getTotalRecord($sql)
	{
		$query = $this->db->query($sql);
		return $this->db->numRows($query);
	}	
		
	
	public function json()
	{
		$page = $_GET['page']; 	// get the requested page
		$limit = $_GET['rows']; // get how many rows we want to have into the grid
		$sidx = $_GET['sidx']; 	// get index row - i.e. user click to sort
		$sord = $_GET['sord']; 	// get the direction
		if(!$page)
		{
			$page =1;
		}
		
		$_SESSION['_GRIDLIST'] = array($this->gridId => array('page' => $page, 'rows' => $limit));
		
		//格式化sql
		$limitSql  = " LIMIT " . ($page - 1) * $limit . ",$limit";
		$orderBySql = " ORDER BY $sidx $sord";
		//去掉order by
		$pattern = "/[\s]+order[\s]+by[\s]*.*/i";
		$replacement = "";
		$this->sql = preg_replace($pattern, $replacement, $this->sql);
		
		$where = $this->subWhere();
		$sql = $this->sql; 
		if (stristr(substr($sql,strripos($sql, ' FROM')), 'where') || stristr(substr($sql,strripos($sql, "\nFROM")), 'where'))
		{
			$sql .= $where;
		}
		else
		{
			$sql .= " WHERE 1 " . $where;
		}
		
		
		$this->sql = $sql;
		$totalRecord = $this->getTotalRecord($this->sql);
		$responce->page = $page;
		$responce->total = ceil($totalRecord / $limit);
		$responce->records = $totalRecord;		
		
		//打印json
		$sql .= $orderBySql . $limitSql;
		$dt = $this->db->fetchAll($sql);
		
		foreach ($dt as $key => $val)
		{

			$tmp =  array();
			$i = 0;
			foreach ($val as $k => $v)
			{
				//默认第一个为key
				if ($i == 0)
				{
					$responce->rows[$key]['id'] = $v;
				} 
				
 				if ($val[$this->key] == $v)
				{
					$responce->rows[$key]['id'] = $val[$this->key];
				}

				$tmp[] = $v;
				$i++;
			}
			$tmp[$this->keyLinkId] = $this->keyLink ?  str_replace('{key}', $responce->rows[$key]['id'], $this->keyLink) : $tmp[$this->keyLinkId];
			$responce->rows[$key]['cell'] = $tmp;
			
		}
		return json_encode($responce);		
		
	}
	
}